﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SistemaAcademicoBO;
using System.Configuration;
using System.Data;
using SistemaAcademicoBO.Entities;

namespace SistemaAcademicoDAL
{
    public class StudentsDAO : AbstratcDAO, IDAO<IEntity>
    {
        public StudentsDAO()
        {
            connection = new Connection();
        }

        public IEntity GetRecord<K>(K id)
        {
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
            connection.OpenConnection();

            ProcedureCall procedureCall = new ProcedureCall("pr_retrieveStudent", connection.GetSQLConnection());
            procedureCall.Parameters.Add(new ProcedureParam("@MatriculaId", SqlDbType.UniqueIdentifier, 0, id));

            procedureCall.Execute(true);

            List<IEntity> resultSet = procedureCall.ExtractFromResultSet(typeof(Student));

            connection.CloseConnection();

            if (resultSet.Count != 1)
                return null;

            return resultSet[0];
        }

        public List<IEntity> GetAllRecords()
        {
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
            connection.OpenConnection();

            ProcedureCall procedureCall = new ProcedureCall("pr_retrieveAllStudents", connection.GetSQLConnection());

            procedureCall.Execute(true);

            List<IEntity> resultSet = procedureCall.ExtractFromResultSet(typeof(Student));

            connection.CloseConnection();

            return resultSet;
        }

        public void SetRecord(IEntity entity)
        {
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
            connection.OpenConnection();
            ProcedureCall procedureCall = new ProcedureCall("pr_storeStudent", connection.GetSQLConnection());

            Student student = (Student)entity;

            procedureCall.Parameters.Add(new ProcedureParam("@MatriculaId", SqlDbType.UniqueIdentifier, 0, student.MatriculaId));
            procedureCall.Parameters.Add(new ProcedureParam("@Nome", SqlDbType.VarChar, 100, student.Nome));
            procedureCall.Parameters.Add(new ProcedureParam("@Cpf", SqlDbType.Char, 11, student.Cpf));
            procedureCall.Parameters.Add(new ProcedureParam("@Nascimento", SqlDbType.Date, 0, student.Nascimento));
            procedureCall.Parameters.Add(new ProcedureParam("@Sexo", SqlDbType.Char, 1, student.Sexo));
            procedureCall.Parameters.Add(new ProcedureParam("@Formado", SqlDbType.Bit, 100, student.Formado));
            procedureCall.Parameters.Add(new ProcedureParam("@Rg", SqlDbType.VarChar, 10, student.Rg));
            procedureCall.Parameters.Add(new ProcedureParam("@Endereco", SqlDbType.VarChar, 100, student.Endereco));
            procedureCall.Parameters.Add(new ProcedureParam("@Cep", SqlDbType.VarChar, 8, student.Cep));
            procedureCall.Parameters.Add(new ProcedureParam("@Telefone", SqlDbType.VarChar, 15, student.Telefone));
            procedureCall.Parameters.Add(new ProcedureParam("@Email", SqlDbType.VarChar, 100, student.Email));
            procedureCall.Parameters.Add(new ProcedureParam("@DegreeCourseId", SqlDbType.Int, 0, student.DegreeCourseId));
            procedureCall.Parameters.Add(new ProcedureParam("@DtIngresso", SqlDbType.Date, 0, student.DtIngresso));
            procedureCall.Parameters.Add(new ProcedureParam("@Periodo", SqlDbType.Char, 1, student.Periodo));
            procedureCall.Parameters.Add(new ProcedureParam("@UserId", SqlDbType.Int, 0, student.UserId));

            procedureCall.Execute(false);

            connection.CloseConnection();
        }

        public void RemoveRecord<K>(K id)
        {
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
            connection.OpenConnection();

            ProcedureCall procedureCall = new ProcedureCall("pr_removeStudent", connection.GetSQLConnection());

            procedureCall.Parameters.Add(new ProcedureParam("@MatriculaId", SqlDbType.Int, 0, id));

            procedureCall.Execute(false);

            connection.CloseConnection();
        }
    }
}
